package org.emet.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateUtils;
import org.emet.Constants;
import org.emet.entity.biz.Freezer;
import org.emet.entity.biz.FreezerCase;
import org.emet.entity.biz.FreezerRent;
import org.emet.jee.core.dao.Page;
import org.emet.jee.core.dao.arch.ArchDataSourceBaseDao;
import org.emet.service.dto.DatePair;
import org.emet.service.dto.FreezerBoxStatus;
import org.emet.service.dto.FreezerCriteria;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class FreezerDao extends ArchDataSourceBaseDao<FreezerRent, Long> {
	private static Logger logger = LoggerFactory.getLogger(FreezerDao.class);
	@Autowired
	private FreezerBoxDao freezerBoxDao;
	@Autowired
	private FreezerCaseDao freezerCaseDao;

	public List<FreezerRent> findBusy(Long freezerId) {
		Date now = new Date();
		Query q = getSession().createQuery("from FreezerRent where freezerId=? and ? >= startDate order by id desc");
		Freezer freezer = freezerBoxDao.get(freezerId);
		if (freezer.getCaseCount() != null) {
			q.setFirstResult(0);
			q.setMaxResults(freezer.getCaseCount());
		}
		q.setParameter(0, freezerId);
		q.setParameter(1, now);

		return q.list();
	}

	@Override
	public void save(FreezerRent entity) {
		entity.setStartDate(DateUtils.setSeconds(entity.getStartDate(), 0));
		entity.setExpectEndDate(DateUtils.setSeconds(entity.getExpectEndDate(), 0));
		if (entity.getEndDate() != null) {
			entity.setEndDate(DateUtils.setSeconds(entity.getEndDate(), 0));
		}
		super.save(entity);
	}

	public Page<FreezerRent> search(Page<FreezerRent> page, FreezerCriteria criteria) {
		Criteria c = getSession().createCriteria(FreezerRent.class);
		NonEmptyValueHashMap<String, Object> propertyNameValues = new NonEmptyValueHashMap<String, Object>();
		//Registration start
		propertyNameValues.put("registration.bizNo", criteria.getBizNo());
		propertyNameValues.put("registration.deadName", criteria.getDeadName());
		propertyNameValues.put("registration.dpyName", criteria.getDpyName());
		propertyNameValues.put("registration.carriageDate", criteria.getCarriageDate());
		propertyNameValues.put("registration.vehicleLpn", criteria.getVehicleLpn());
		propertyNameValues.put("registration.client", criteria.getClient());
		propertyNameValues.put("registration.cPyName", criteria.getcPyName());
		propertyNameValues.put("registration.phone", criteria.getPhone());
		//Registration end

		propertyNameValues.put("freezerName", criteria.getFreezerName());
		propertyNameValues.put("startDate", criteria.getStartDate());
		propertyNameValues.put("endDate", criteria.getEndDate());
		if (criteria.getTotalFee() != 0d) {
			propertyNameValues.put("totalFee", criteria.getTotalFee());
		}
		propertyNameValues.put("remark", criteria.getRemark());
		c.add(Restrictions.allEq(propertyNameValues));
		page.setOrderBy("id");
		page.setOrder("desc");
		return findPage(page, c);
	}

	public List<FreezerBoxStatus> obtainLastestStatus() {
		String hql = "from FreezerRent where freezerId=? and caseId=? and endDate is null and ? >= startDate  order by id desc";
		List<Freezer> all = freezerBoxDao.getAll();
		Date nowDate = new Date();
		List<FreezerBoxStatus> result = new ArrayList<FreezerBoxStatus>(all.size());
		for (Freezer freezer : all) {
			List<FreezerCase> allCases = freezerCaseDao.findBy("freezerId", freezer.getId());
			for (FreezerCase case1 : allCases) {
				FreezerBoxStatus freezerBoxStatus = new FreezerBoxStatus();
				freezerBoxStatus.setFreezerName(freezer.getName() + " -- " + case1.getName());
				if (!(Constants.STATUS_INVALID + "").equalsIgnoreCase(freezer.getStatus())) {
					freezerBoxStatus.setStatus(Constants.STATUS_OK + "");
				}
				//Mark the busy one
				List<FreezerRent> rentList = find(hql, freezer.getId(), case1.getId(), nowDate);
				if (rentList.size() > 1) {
					logger.error("Only one FreezerRent needed but is {}", rentList.size());
				}
				//boolean busy = false;
				for (FreezerRent freezerRent : rentList) {
					/*if (freezerRent.getEndDate() != null) {
						if (nowDate.before(freezerRent.getEndDate())) {
							busy = true;
						}
					}  else if (nowDate.before(freezerRent.getExpectEndDate())) {
						busy = true;
					}

					if (busy) {*/
					freezerBoxStatus.setStartDate(freezerRent.getStartDate());
					freezerBoxStatus.setExpectEndDate(freezerRent.getExpectEndDate());
					// freezerBoxStatus.setDeadName(freezerRent.getRegistration().getDeadName());
					freezerBoxStatus.setStatus(Constants.STATUS_BUSY + "");
					if ((Constants.STATUS_INVALID + "").equalsIgnoreCase(freezer.getStatus())) {
						freezer.setStatus(Constants.STATUS_BUSY + "");
					}
					break;
					//}
				}

				result.add(freezerBoxStatus);
			}

		}

		return result;
	}

	public List<DatePair> getBusyTimeRange(Long id, Long freezerId, Long caseId) {
		String hql = " select new org.emet.service.dto.DatePair(startDate,expectEndDate) from "
				+ entityClass.getSimpleName() + " where ";
		if (id != null) {
			hql += " id<>? and ";
		}
		hql += "freezerId = ? and caseId=? and endDate is null and expectEndDate > now() order by startDate asc";
		if (id != null)
			return find(hql, id, freezerId, caseId);
		else
			return find(hql, freezerId, caseId);
	}

	public List<DatePair> getIdleTimeRange(Long id, Long freezerId, Long caseId) {
		String hql = " select new org.emet.service.dto.DatePair(startDate,expectEndDate) from "
				+ entityClass.getSimpleName() + " where  ";
		if (id != null) {
			hql += " id<>? and ";
		}
		hql += " freezerId = ? and caseId=? and expectEndDate > now() order by startDate asc";
		List<DatePair> busyDatePairs = null;
		if (id != null) {
			busyDatePairs = find(hql, id, freezerId, caseId);
		} else {
			busyDatePairs = find(hql, freezerId, caseId);
		}
		List<DatePair> idleDatePairs = new ArrayList<DatePair>(busyDatePairs.size() + 1);
		Date beginDate = new Date();
		for (DatePair idp : busyDatePairs) {
			Date endDate = idp.getStartDatetime();
			if (isBefore(beginDate, endDate)) {
				idleDatePairs.add(new DatePair(beginDate, endDate));
			}
			beginDate = idp.getEndDatetime();
		}
		Date endDate = setTime(beginDate, 23, 59, 59);
		if (isBefore(beginDate, endDate)) {
			idleDatePairs.add(new DatePair(beginDate, endDate));
		}

		return idleDatePairs;
	}

	private boolean isBefore(Date d1, Date d2) {
		return d1.before(d2);
	}

	private Date setTime(Date beginDate, int hour, int min, int sec) {
		Calendar instance = Calendar.getInstance();
		instance.setTime(beginDate);
		instance.set(Calendar.HOUR_OF_DAY, hour);
		instance.set(Calendar.MINUTE, min);
		instance.set(Calendar.SECOND, sec);
		return instance.getTime();
	}

	public List<Map<String, Object>> searchByYear(int startYear, int endYear, boolean groupByName) {
		if (!groupByName)
			return getJdbcTemplate()
					.query("SELECT freezer_name,YEAR(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_FREEZER_RENT WHERE YEAR(START_DATE)>=? AND YEAR(START_DATE) <= ? GROUP BY YEAR(START_DATE)",
							reportRowMap, startYear, endYear);
		else
			return getJdbcTemplate()
					.query("SELECT freezer_name,YEAR(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM   T_FREEZER_RENT WHERE YEAR(START_DATE)>=? AND YEAR(START_DATE) <= ?  GROUP BY freezer_name,YEAR(START_DATE) ORDER BY freezer_name DESC",
							reportRowMap, startYear, endYear);
	}

	public List<Map<String, Object>> searchByMon(int startYear, int startMon, int endYear, int endMon,
			boolean groupByName) {
		if (!groupByName)
			return getJdbcTemplate()
					.query("SELECT freezer_name,CONCAT(YEAR(START_DATE),'-',MONTH(START_DATE)) AS DATESTRING,COUNT(ID) AS COUNT FROM T_FREEZER_RENT WHERE (YEAR(START_DATE)>=?  AND MONTH(START_DATE) >= ?) AND (YEAR(START_DATE) <= ? AND MONTH(START_DATE) <= ?) GROUP BY YEAR(START_DATE), MONTH(START_DATE)",
							reportRowMap, startYear, startMon, endYear, endMon);
		else
			return getJdbcTemplate()
					.query("  SELECT freezer_name,CONCAT(YEAR(START_DATE),'-',MONTH(START_DATE)) AS DATESTRING,COUNT(ID) AS COUNT FROM T_FREEZER_RENT WHERE (YEAR(START_DATE)>=?  AND MONTH(START_DATE) >= ?) AND (YEAR(START_DATE) <= ? AND MONTH(START_DATE) <= ?) GROUP BY freezer_name,YEAR(START_DATE),MONTH(START_DATE) ORDER BY freezer_name DESC",
							reportRowMap, startYear, startMon, endYear, endMon);
	}

	public List<Map<String, Object>> searchByDate(Date startDate, Date endDate, boolean groupByName) {
		if (!groupByName)
			return getJdbcTemplate()
					.query(" SELECT freezer_name,DATE(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_FREEZER_RENT  WHERE date(START_DATE)>=DATE(?) AND DATE(START_DATE) <= DATE(?) GROUP BY DATE(START_DATE)",
							reportRowMap, startDate, endDate);
		else
			return getJdbcTemplate()
					.query(" SELECT freezer_name,DATE(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_FREEZER_RENT WHERE date(START_DATE)>=DATE(?) AND date(START_DATE) <= DATE(?)  GROUP BY freezer_name,DATE(START_DATE) ORDER BY freezer_name",
							reportRowMap, startDate, endDate);
	}

	ParameterizedRowMapper<Map<String, Object>> reportRowMap = new ParameterizedRowMapper<Map<String, Object>>() {
		public Map<String, Object> mapRow(ResultSet rs, int paramInt) throws SQLException {
			Map<String, Object> c = new HashMap<String, Object>(1);
			c.put("count", rs.getInt("COUNT"));
			c.put("dateString", rs.getString("DATESTRING"));
			c.put("freezerName", rs.getString("FREEZER_NAME"));
			return c;
		}
	};

	public List<FreezerRent> findUnpaiedByBiz(String bizNo) {
		return find("from FreezerRent where paied = false and registration.bizNo = ?", bizNo);
	}

	public void setAsPaied(Long chargeId) {
		FreezerRent freezerRent = get(chargeId);
		if (freezerRent != null) {
			freezerRent.setPaied(true);
		}

	}
}
